package ATBMS.modles.DAO;

import ATBMS.modles.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * User DAO Implement
 */
public class UserDAOImpl implements UserDAO {

    private Connection conn;
    private String errorMessage;

    public List<User> findByLevel(int level) {
        List<User> ret = new ArrayList<User>();
        try {

            PreparedStatement ps = conn.prepareStatement("SELECT uid,uflag,uname,uaddr,utel," +
                    "uemail FROM atbms_user WHERE uflag=?");
            ps.setInt(1, level);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ret.add(new User(rs.getInt(1), rs.getInt(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(6)));
            }
            rs.close();
            ps.close();
            return ret;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public User findByName(String name) {
        User ret = null;
        try {

            PreparedStatement ps = conn.prepareStatement("SELECT uid,uflag,uname,uaddr,utel," +
                    "uemail FROM atbms_user WHERE uname=?");
            ps.setString(1, name);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = new User(rs.getInt(1), rs.getInt(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(6));
            }
            rs.close();
            ps.close();
            return ret;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    private UserDAOImpl(Connection conn) {
        this.conn = conn;
    }

    static public UserDAO getInstance(Connection conn) {
        return new UserDAOImpl(conn);
    }

    public void delete(int uid) {
        try {
            PreparedStatement ps = conn.prepareStatement("DELETE FROM atbms_user " +
                    " WHERE uid=?");
            ps.setInt(1, uid);
            ps.execute();
            SQLWarning sqlError = ps.getWarnings();
            ps.close();
            setErrorMessage(sqlError);
        } catch (SQLException e) {
            setErrorMessage(e);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public User validateUser(User user) {
        User ret = null;
        try {
            PreparedStatement ps = conn.prepareStatement("SELECT uid,uflag,uname,uaddr," +
                    "utel,uemail FROM atbms_user WHERE uname=? AND upassword=MD5(?)");
            ps.setString(1, user.getUname());
            ps.setString(2, user.getPassword());
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                ret = new User(rs.getInt(1), rs.getInt(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(6));
            }
            rs.close();

            return ret;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<User> findAll() {
        List<User> ret = new ArrayList<User>();
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT uid,uflag,uname,uaddr,utel,uemail FROM atbms_user");
            while (rs.next()) {
                ret.add(new User(rs.getInt(1), rs.getInt(2), rs.getString(3),
                        rs.getString(4), rs.getString(5), rs.getString(6)));
            }
            rs.close();
            stmt.close();
            return ret;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public User findByPrimaryKey(int uid) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean insert(User user) {
        try {
            PreparedStatement ps = conn.prepareStatement("INSERT INTO atbms_user" +
                    " SET " +
                    "uflag=?," +
                    "uname=?," +
                    "uaddr=?," +
                    "utel=?," +
                    "uemail=?," +
                    "upassword=MD5(?)");
            ps.setInt(1, user.getUflag());
            ps.setString(2, user.getUname());
            ps.setString(3, user.getUaddr());
            ps.setString(4, user.getUtel());
            ps.setString(5, user.getUemail());
            ps.setString(6, user.getPassword());
            ps.execute();
            SQLWarning sqlError = ps.getWarnings();
            boolean ret = (sqlError == null);
            ps.close();
            setErrorMessage(sqlError);
            return ret;
        } catch (SQLException e) {
            setErrorMessage(e);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public boolean update(int uid, User user) {
        try {
            boolean updatePassword = (user.getPassword().length() != 0);
            PreparedStatement ps = conn.prepareStatement("UPDATE atbms_user SET " +
                    "uflag=?," +
                    "uname=?," +
                    "uaddr=?," +
                    "utel=?," +
                    "uemail=?" +
                    (updatePassword ? ",upassword=MD5(?)" : "") +
                    " WHERE uid=?");
            ps.setInt(1, user.getUflag());
            ps.setString(2, user.getUname());
            ps.setString(3, user.getUaddr());
            ps.setString(4, user.getUtel());
            ps.setString(5, user.getUemail());
            if (updatePassword) {
                ps.setString(6, user.getPassword());
                ps.setInt(7, uid);
            } else {
                 ps.setInt(6, uid);
            }

            ps.execute();
            SQLWarning sqlError = ps.getWarnings();
            boolean ret = (sqlError == null);
            ps.close();
            setErrorMessage(sqlError);
            return ret;
        } catch (SQLException e) {
            setErrorMessage(e);
            return false;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    public String getErrorMessage() {
        return errorMessage;
    }

    private void setErrorMessage(SQLException err) {
        if (err == null) {
            return;
        }

        errorMessage = err.getMessage();
    }

    private void setErrorMessage(SQLWarning warning) {
        if (warning == null) {
            return;
        }

        errorMessage = warning.getMessage();
    }
}
